package com.myersinfosys.protrack.poi;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.DataFormat;


public class XLSReader {
	private static final String DEFAULT_CHARSET = "UTF-8";
	private static final int MAX_COLUMNS = 100;
	private static final String UNL_DELIMITER = "|";
	private static final String UNL_ILLEGAL1 = "\n";
	private static final String UNL_ILLEGAL2 = "\r";
	private static final String BLANK_STRING = " ";
	private static final int MAX_FIELDSIZE = 4096;
	private static final SimpleDateFormat xlDateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	//private static final SimpleDateFormat xlDateFormat = new SimpleDateFormat("MM/dd/yyyy");
	private static final SimpleDateFormat xlTimeFormat = new SimpleDateFormat("HH:mm:ss");
	private static final SimpleDateFormat xlYearFormat = new SimpleDateFormat("yyyy");
	
	private String filename;
	private String unlFilename;
	private List<List<Cell>> sheetData;
	private FileInputStream filestream = null;
	private Workbook workbook;
	private Sheet sheet;
	private int sheetNumber = 0;
	private int nSheets = 1;
	private Boolean readFirstSheetOnly = true;
	private Boolean combineSheets = false;
	private short maxColumns = 0;
	
	public Boolean hasData = false;
	
	
	public XLSReader(String filename, Boolean readFirstSheetOnly, Boolean combineSheets) throws IOException {
		File f = new File(filename);
		String s = f.getName();
		int pos = s.lastIndexOf(".");
		String ext = s.substring(pos+1).toLowerCase();
		
		if(!ext.equals("xls") && !ext.equals("xlsx")) {
			hasData = false;
		} else {
			this.filename = filename;
			this.readFirstSheetOnly = readFirstSheetOnly;
			this.combineSheets = combineSheets;
			
			sheetData = new ArrayList<List<Cell>>();
			
			try {
	            //
	            // Create a FileInputStream that will be used to read the excel file.
	            //
	        	filestream = new FileInputStream(filename);

	            //
	            // Create an excel workbook from the file system.
	            //
	            workbook = WorkbookFactory.create(filestream);
	            
	            
	            nSheets = workbook.getNumberOfSheets();
	            
	        } catch (IOException e) {
	            e.printStackTrace();
	        } catch (InvalidFormatException e) {
				e.printStackTrace();
			} finally {
	            if (filestream != null) {
	            	filestream.close();
	            }
	        }
		}
	}
	
	
	public int getNSheets() {
		return nSheets;
	}
	
	
	public Boolean readData(int sheetNum) throws IOException, InvalidFormatException {

		sheetData.clear();
		this.hasData = false;
		
		if (sheetNum < this.nSheets) {
			
			this.sheetNumber = sheetNum;
			
            //
            // Get the Nth sheet on the workbook.
            //
            sheet = workbook.getSheetAt(sheetNumber);

            
            // get the maximum number of columns
            if(readFirstSheetOnly || !combineSheets || (combineSheets && sheetNumber==0))
            {
	            Iterator<Row> rows = sheet.rowIterator();
	            while (rows.hasNext()) {
	                Row row = (Row) rows.next();
	
	                if(row.getLastCellNum() > maxColumns)
	                	maxColumns = row.getLastCellNum();
	            }
	            if(maxColumns > MAX_COLUMNS)
	            	maxColumns = MAX_COLUMNS;
        	}
        
            //
            // When we have a sheet object in hand we can iterator on each
            // sheet's rows and on each row's cells. We store the data read
            // on an ArrayList.
            //
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = (Row) rows.next();
                List<Cell> data = new ArrayList<Cell>();

                for(int i=0;  i<maxColumns; i++) {
                    Cell cell = row.getCell(i);
                    if(cell == null) {
                    		cell = row.createCell(i, Cell.CELL_TYPE_STRING);                    	
								cell.setCellValue(BLANK_STRING);
						  }	
                    
                    data.add(cell);	
                }

                sheetData.add(data);
            }
            
            this.hasData = true;
		}
        
        return this.hasData;
	}
	

   

	public void writeUnlData(String unlFilename) {
    	
    	String cellData = null;
    	BufferedWriter unlFile = null;
    	
    	if (!this.hasData)
    		return;
    	
    	this.unlFilename = unlFilename;
    	
    	try {
			unlFile = createOutFile(unlFilename);	

	        //
	        // Iterates the data and prints it out to a unl file.
	        //
			  // rows			
	        for (int i = 0; i < sheetData.size(); i++) {
	            List<?> list = (List<?>) sheetData.get(i);
	            //columns
	            for (int j = 0; j < list.size() && j < maxColumns; j++) {
	            	if (j == 0) {
	            		// write out the id
	            		cellData = new Integer(i+1).toString() + UNL_DELIMITER;
	            		unlFile.write(cellData);
	            		
	            		// name of workbook
	            		//cellData = new Integer(maxColumns).toString() + UNL_DELIMITER;
	            		File file = new File(filename); 
	            		cellData = file.getName() + UNL_DELIMITER;
	            		unlFile.write(cellData);
	            		// sheet name
	            		cellData = workbook.getSheetName(sheetNumber) + UNL_DELIMITER;
	            		unlFile.write(cellData);
	            		//System.out.print(cellData);
	            	}

	               Cell cell = (Cell) list.get(j);
	               cellData = getCellAsString(cell);
	                
	               cellData = cellData.replace(UNL_DELIMITER, " ");
	               cellData = cellData.replace(UNL_ILLEGAL1, " ");
	               cellData = cellData.replace(UNL_ILLEGAL2, " ");
	               if (cellData.length() == 0 || cellData == null) {
	            	   cellData = " ";
						} else {
							if (cellData.length() > MAX_FIELDSIZE)
								cellData = cellData.substring(0,MAX_FIELDSIZE);
	               }
	               cellData = cellData + UNL_DELIMITER;
	               unlFile.write(cellData);
	               //System.out.print(cellData);
	            }
	            
	            for (int x = list.size(); x <maxColumns; x++) {
	            	unlFile.write(UNL_DELIMITER);
	            	//System.out.print(UNL_DELIMITER);
	            }
	            unlFile.newLine();
	            //System.out.println("");
	        }

	        if (unlFile != null) {
	        	unlFile.flush();
	        	unlFile.close();
	        } 
		} catch (IOException e) {
			e.printStackTrace();
		}
    }	

    public void writeSqlCreate(String tableName, String sqlFilename) {
    	
    	int colCount = 0;
    	String cellData;
    	int[] colSize = new int[maxColumns];
    	String[] colHeader = new String[maxColumns];
 
    	if (!this.hasData)
    		return;
    	
    	try {
	        //
	        // Iterates the data and prints it out to a sql file.
	        //
	        for (int i = 0; i < sheetData.size(); i++) {
	            List<?> list = (List<?>) sheetData.get(i);
	            
	            for (int j = 0; j < list.size() && j < maxColumns; j++) {
	                Cell cell = (Cell) list.get(j);
	                cellData = getCellAsString(cell);

	                if(i == 0) {
	                	colCount = (list.size()<maxColumns) ? list.size() : maxColumns;
	            		colSize[j] = 1;
	            		colHeader[j] = "c" + Integer.toString(j+1);
	                }
						 
	                byte[] nbytes = cellData.getBytes(DEFAULT_CHARSET);
	                if(nbytes.length > colSize[j] && nbytes.length < MAX_FIELDSIZE)
	                	colSize[j] = nbytes.length;
	 
	             }
	         }
	        

	        
			System.out.println(tableName);
			String sBuf = "";
			
			if(readFirstSheetOnly || !combineSheets || (combineSheets && sheetNumber==0)) {
				sBuf = "DROP TABLE " + tableName + ";\n" + 
					   "CREATE TABLE " + tableName + 
			  			" (id integer,name char(40),sheet char(40),";
				for (int j = 0; j < colCount; j++) {
						String colName = colHeader[j].toLowerCase();
						if(colName.length() > 18)
							colName = colName.substring(0, 18);
						
						if(colName.length() > 0) {
							sBuf = sBuf + colName + 
	        					" char(" + colSize[j] + ")" + ((j<colCount-1) ? "," : ");\n");
						}
				}
				sBuf = sBuf + " grant all on " + tableName + " to protrack with grant option;\n";
				sBuf = sBuf + " grant all on " + tableName + " to public;\n";
			}

			sBuf = sBuf + "LOAD FROM \"" + unlFilename + "\" INSERT INTO " + tableName + ";\n"; 
        
	        BufferedWriter sqlFile = createOutFile(sqlFilename);
        	
            if (sqlFile != null) {
            	sqlFile.append(sBuf);
            	//sqlFile.write(sBuf);
            	sqlFile.flush();
            	sqlFile.close();
            	System.out.println(sBuf);
            }            

		} catch (IOException e) {
			e.printStackTrace();
		}

    }	
    
    public String makeValidTableName(String tableName) {
        int maxTableNameSize = 18 - 2;  // 11 minus 'xl' prefix
        
        tableName = tableName.replace(" ", "_");
        
        if(!readFirstSheetOnly) 
        	maxTableNameSize -= 2;  // account for sheet numbering 00-99
        
        if(tableName.length() > maxTableNameSize) 
        	tableName = tableName.substring(0, maxTableNameSize);
        
        tableName = "xl" + tableName.toLowerCase();
        
        if(!readFirstSheetOnly && !combineSheets) {	
			tableName += String.format("%02d", sheetNumber+1);
		}   
        
        return tableName;
    }
    
    
    private BufferedWriter createOutFile(String fileName) {
    	BufferedWriter buffFile = null;

    	try {
			buffFile = new BufferedWriter(
						new OutputStreamWriter(
								new FileOutputStream(fileName, true), DEFAULT_CHARSET));
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    	
    	return buffFile;
    }
    

	 private Boolean isCellDateTime(Cell cell) {
	 	
		Boolean ret_value = false;

		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
         DataFormat df = workbook.createDataFormat();
         String dfstring = df.getFormat(cell.getCellStyle().getDataFormat());

         if(DateUtil.isCellDateFormatted(cell) || dfstring.contains("yy") || dfstring.contains(":"))
				ret_value = true;
			else
				ret_value = false;
		}

	   return ret_value; 	
	 }
    

    private String getCellAsString(Cell cell) {
    	String cellData;
    	
        switch(cell.getCellType()) {
	    	case Cell.CELL_TYPE_NUMERIC :

	    		if(isCellDateTime(cell)) {

					  if(new Integer(xlYearFormat.format(cell.getDateCellValue())).intValue() < 1970) {
						  cellData = xlTimeFormat.format(cell.getDateCellValue());
					  }  
					  else {	  
	    	        	  cellData = xlDateTimeFormat.format(cell.getDateCellValue());
					  }	  
				} else {
					cellData = Double.toString(cell.getNumericCellValue());
					if(cellData.endsWith(".0"))
						cellData = cellData.replaceAll("\\.0", "");
				}	
	    		break;
	    	case Cell.CELL_TYPE_STRING :
	    		cellData = cell.getRichStringCellValue().getString().trim();
	    		break;
	    	case Cell.CELL_TYPE_BOOLEAN:
	            cellData = Boolean.toString(cell.getBooleanCellValue());
	            break;
	        case Cell.CELL_TYPE_FORMULA:
	            cellData = cell.getCellFormula();
	            break;
	    	default : 
	    		cellData = "";
	    		break;
        }    	
        
        return cellData.trim();
    }
    

}

